
		**********************************************************
		** File: cleaning_UNODC.do								**
		** Paper: Human Trafficking Indicators: A New Dataset	**
		** Author: Richard Frank								**
		** Date: July 11, 2021									**
		** Task: Cleaning UNODC drug and crime data				**
 		**********************************************************

	*** UNODC DRUG AND CRIME DATA ***

	clear
	version 16.0
	set seed 1234
								
								************
								** HEROIN **
								************
								
	* Source: https://dataunodc.un.org/data/drugs/Annual%20Drug%20Seizures
	import excel "6.1.2_Drug_seizures_-_national_data.xls", sheet("Seizures 2012-2016") firstrow case(lower) clear
	
	tab drug
	keep if drug=="Heroin"
	keep country year specifics amount unit
	gen grams=.
	replace grams=amount if unit=="Gram"
	replace grams=(amount*1000) if unit=="Kilogram"
	drop if grams==.
	drop specifics amount unit
	rename grams heroin
	rename country Country
	run "cow.do"
	sort ccode year
	drop if ccode==0
	duplicates report ccode year
	tab Country  if ccode==ccode[_n-1] & year==year[_n-1]
	collapse (sum) heroin (first) Country, by(ccode year)							  
	label var heroin "Heroin seized (grams) UNODC"							  
	save "heroin.dta", replace
							
							*****************
							*** HOMICIDES ***
							*****************
							
	* data from: https://dataunodc.un.org/content/data/homicide/homicide-rate
	
	import excel "Intentional homicide victims, total counts and rat.xls", sheet("Total Count and Rate") clear
	rename C country
	rename W hom2000
	rename X hom2001
	rename Y hom2002
	rename Z hom2003
	rename AA hom2004
	rename AB hom2005
	rename AC hom2006
	rename AD hom2007
	rename AE hom2008
	rename AF hom2009
	rename AG hom2010
	rename AH hom2011	
	rename AI hom2012
	rename AJ hom2013
	rename AK hom2014
	rename AL hom2015
	rename AM hom2016 								
	drop A B D E F G H I J K L M N O P Q R S T U V
	drop in 1/6
	drop if country==""
	destring hom2000, replace
	reshape long hom, i(country) j(year)
	rename hom homicides
	rename country Country
	run "cow.do"
	sort ccode year
	collapse (first) Country (mean) homicides, by(ccode year)
	label var hom "Homicides per 100000 people (UNODC)"
	save "homicides.dta", replace
